package DAO;

import java.sql.*;
import connect.ConnectDB;
import java.util.Vector;
import javax.swing.table.DefaultTableModel;

public class DesignationDAO {

    Connection conn;
    ResultSet rsDes = null;
    Statement st;
    PreparedStatement pst;
    ConnectDB cdb;

    public DesignationDAO() {
        cdb = new ConnectDB();
        cdb.getConnect();
        this.conn = cdb.conn;
        loadDataDes();
    }

    public void loadDataDes() {
        try {
            String query = "SELECT "
                    + "             * "
                    + "     FROM "
                    + "             Designation";
            st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            rsDes = st.executeQuery(query);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public String checkDesId(String DesId) {
        try {
            loadDataDes();
            while (rsDes.next()) {
                if (rsDes.getString("DESIGNID").equals(DesId)) {
                    return "Exist";
                }
            }
            return "Not exist";
        } catch (Exception e) {
            e.printStackTrace();
            return "Error";
        }
    }

    public Vector loadDesToUpdate(String DesId) {
        // create vector to store Department data (1 row will be store)
        Vector vecDesData = new Vector();
        try {
            // Move cursor to rist row (this is department data)
            rsDes.beforeFirst();

            // Loop to find the row which have this DepNo
            while (rsDes.next()) {
                if (rsDes.getString("DESIGNID").endsWith(DesId)) {
                    // add all record form this row to vector to show
                    vecDesData.add(rsDes.getObject(1));
                    vecDesData.add(rsDes.getObject(2));
                }
            }

            return vecDesData;
        } catch (Exception e) {
            System.out.println("Error in loadDesToUpdateForm: " + e.getMessage());
            return null;
        }
    }

    public DefaultTableModel loadTableDes() {
        try {
            rsDes.beforeFirst();

            // Load column name
            ResultSetMetaData rsmd = rsDes.getMetaData();

            Vector colName = new Vector();

            colName.add(rsmd.getColumnName(1)); // Dept ID
            colName.add(rsmd.getColumnName(2)); // Dept Name

            DefaultTableModel dtm = new DefaultTableModel(colName, 0);

            // Load row data
            while (rsDes.next()) {
                Vector rowData = new Vector();
                rowData.add(rsDes.getString(1));
                rowData.add(rsDes.getString(2));
                dtm.addRow(rowData);
            }

            return dtm;
        } catch (Exception e) {
            System.out.println("Error in loadDataDes: " + e.getMessage());
            return null;
        }
    }
    
    public boolean insertDes(Vector vecDesData){
        try {
            rsDes.moveToInsertRow();
            for (int i = 0; i < vecDesData.size(); i++) {
                rsDes.updateString(i + 1, vecDesData.get(i).toString());
            }
            rsDes.insertRow();
            loadDataDes();
            return true;
        } catch (Exception e) {
            System.out.println("Error in insertDes: " + e.getMessage());
            return false;
        }
    }
    
    public boolean updateDes(Vector vecDesData) {
        try {
            rsDes.beforeFirst();
            while (rsDes.next()) {
                if (rsDes.getString("DESIGNID").equals(vecDesData.get(0))) {
                    for (int i = 0; i < vecDesData.size(); i++) {
                        rsDes.updateString(i + 1, vecDesData.get(i).toString());
                    }
                    rsDes.updateRow();
                    loadDataDes();
                    return true;
                }
            }

            return false;
        } catch (Exception e) {
            System.out.println("Error in updateEmp: " + e.getMessage());
            return false;
        }
    }
    
    public boolean deleteDes(String DesId) {
        try {
            rsDes.beforeFirst();
            while (rsDes.next()) {
                if (rsDes.getString("DESIGNID").trim().equals(DesId)) {
                    rsDes.deleteRow();
                    loadDataDes();
                    return true;
                }
            }
            return false;
        } catch (Exception e) {
            System.out.println("Error: " + e.getMessage());
            return false;
        }
    }
    
    public void closeConnect() {
        try {
            if (rsDes != null) {
                rsDes.close();
            }

            if (pst != null) {
                pst.close();
            }

            if (st != null) {
                st.close();
            }

            if (conn != null) {
                conn.close();
            }

            if (cdb.conn != null) {
                cdb.closeConnect();
            }
            System.out.println("Close connect: Designation");
        } catch (Exception e) {
            System.out.println("Error in close connection: " + e.getMessage());
        }
    }
}
